package com.demo.las.repository;

import com.demo.las.entity.StudyRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

@Repository
public interface StudyRecordRepository extends JpaRepository<StudyRecord, Long> {
    
    List<StudyRecord> findByUserIdOrderByStudyDateDesc(Long userId);
    
    List<StudyRecord> findByUserIdAndStudyDateBetween(Long userId, LocalDate startDate, LocalDate endDate);
    
    List<StudyRecord> findByUserIdAndSubject(Long userId, String subject);
    
    List<StudyRecord> findByUserIdAndSubjectOrderByStudyDateDesc(Long userId, String subject);
    
    List<StudyRecord> findByUserIdAndStudyDate(Long userId, LocalDate studyDate);
    
    @Query("SELECT sr FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate ORDER BY sr.studyDate DESC")
    List<StudyRecord> findByUserIdAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT SUM(sr.studyTime) FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate")
    BigDecimal getTotalStudyTimeByUserAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT AVG(sr.efficiencyScore) FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate")
    BigDecimal getAverageEfficiencyByUserAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT AVG(sr.qualityScore) FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate")
    BigDecimal getAverageQualityByUserAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT SUM(sr.studyTime * sr.efficiencyScore * sr.qualityScore) FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate")
    BigDecimal getTotalEffectiveStudyTimeByUserAndDateRange(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT sr.subject, SUM(sr.studyTime) as totalTime, AVG(sr.efficiencyScore) as avgEfficiency, AVG(sr.qualityScore) as avgQuality FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate GROUP BY sr.subject")
    List<Object[]> getStudyStatisticsBySubject(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT sr.studyDate, SUM(sr.studyTime) as dailyTime, AVG(sr.efficiencyScore) as dailyEfficiency FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate GROUP BY sr.studyDate ORDER BY sr.studyDate")
    List<Object[]> getDailyStudyStatistics(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT COUNT(DISTINCT sr.studyDate) FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate")
    Long getStudyDaysCount(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT sr FROM StudyRecord sr WHERE sr.userId = :userId AND sr.efficiencyScore >= :minEfficiency ORDER BY sr.studyDate DESC")
    List<StudyRecord> findHighEfficiencyRecords(@Param("userId") Long userId, @Param("minEfficiency") BigDecimal minEfficiency);
    
    @Query("SELECT sr FROM StudyRecord sr WHERE sr.userId = :userId AND sr.qualityScore >= :minQuality ORDER BY sr.studyDate DESC")
    List<StudyRecord> findHighQualityRecords(@Param("userId") Long userId, @Param("minQuality") BigDecimal minQuality);
    
    // 以下方法为StudyRecordService所需
    @Query("SELECT SUM(sr.studyTime) FROM StudyRecord sr WHERE sr.userId = :userId AND sr.subject = :subject AND sr.studyDate BETWEEN :startDate AND :endDate")
    BigDecimal getTotalStudyTimeByUserSubjectAndDateRange(@Param("userId") Long userId, @Param("subject") String subject, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT sr.subject, SUM(sr.studyTime) as totalTime FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate GROUP BY sr.subject")
    List<Object[]> getStudyTimeBySubject(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT sr.studyDate, SUM(sr.studyTime) as dailyTime FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate GROUP BY sr.studyDate ORDER BY sr.studyDate")
    List<Object[]> getDailyStudyTimeTrend(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT COUNT(DISTINCT sr.subject) FROM StudyRecord sr WHERE sr.userId = :userId AND sr.studyDate BETWEEN :startDate AND :endDate")
    Long getDistinctSubjectCount(@Param("userId") Long userId, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);
}
